# -*- coding: utf-8 -*-
from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class DamengManager(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()


    def define_col(self):
        """
        定义mysql插入字段类型
        :return:
        """
        col = {
            "ID": "BIGINT NOT NULL AUTO_INCREMENT,",
            "C_01_VARCHAR": "VARCHAR(50),",
            "C_02_INT": "INT NULL,",
            "C_03_DATE": "DATE NULL, ",
            "": "PRIMARY KEY (ID) )"
        }
        return col


    def define_ddl(self, db, table):
        """
        拼接mysql ddl语句
        :param table:
        :return:
        """
        col = self.define_col()

        ddl_sql = 'create table {}.{} ( \r\n'.format(db, table)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        print(ddl_sql)
        return ddl_sql

    def general_col_value(self, db, table):
        """
        生成 mysql dml
        :param table:
        :return:
        """

        C_02_INT = self.get_random_float(100, 500)
        c_03_double_val = self.get_random_float(100, 500)
        c_04_float_val = self.get_random_float(10, 500)
        c_05_smallint_val = self.get_random_float(100, 500)
        c_06_bigint_val = self.get_random_float(100, 500)
        C_01_VARCHAR = self.get_random_string(8)
        C_03_DATE = self.get_random_date()
        sql = "insert into {}.{} (" \
              " C_01_VARCHAR, C_02_INT, C_03_DATE) " \
              "values ('{}');" \
              "".format(db, table, C_01_VARCHAR, C_02_INT, C_03_DATE)
        return sql

    def generate_dml_sql(self, databaseName, tableName, num):
        """
        生成 Oracle 11g DML 插入语句的值。
        :param database: 数据库名称
        :param table: 表名称
        :param num: 生成的记录数量
        :return: tuple (SQL template, list of tuples with values)
        """
        # 获取已有记录的数量
        res_num = self.get_count(databaseName, tableName)

        sql_template = f"insert INTO {databaseName}.{tableName} (ID, " \
                       f"C_01_VARCHAR, C_02_INT, C_03_DATE) VALUES " \
                       f"(?, ?, ?, TO_DATE(?, 'YYYY-MM-DD'))"

        values_list = []
        for i in range(res_num + 1, res_num + num + 1):
            values_list.append((
                i,
                self.get_random_string(5),
                self.get_random(-922, 922),
                self.get_random_date(),
            ))
        print(sql_template, values_list)
        return sql_template, values_list

    def execute_drop_table(self, db, table):
        sql = 'drop TABLE {}."{}"'.format(db, table)
        res = self.exec_dameng_sql(db, sql)
        return self.set_return_massage(res)


    def cdc_set_on(self, db, table):
        sql = "grant SELECT on {}.{} to DMREAD".format(db, table)
        res = self.exec_dameng_sql(db, sql)
        return self.set_return_massage(res)

    def execute_deleted_data(self, db, table):
        sql = ("delete FROM {}.{} WHERE ID IN (SELECT ID FROM (SELECT ID FROM {}.{} "
               "ORDER BY ID DESC)WHERE ROWNUM <= 2)").format(db, table, db, table)
        res = self.exec_dameng_sql(db, sql)
        return self.set_return_massage(res)

    def execute_update_data(self, db, table):
        # 获取列名（移除引号并大写）
        sql = "select column_name FROM user_tab_columns WHERE table_name = '{}'".format(table.upper())
        res = self.exec_dameng_sql(db, sql)

        print(res)

        if res['message'] != "Success":
            return False

        # 获取所有列名，排除 ID_VAL
        columns = [row[0] for row in res['data'] if row[0].lower() != 'id_val']

        # 获取最近的10个ID_VAL
        get_ids_sql = ("SELECT ID FROM (SELECT ID FROM {}.{} ORDER BY ID DESC) "
                       "WHERE ROWNUM <= 10").format(db, table)

        print(get_ids_sql)
        ids_res = self.exec_dameng_sql(db, get_ids_sql)
        if ids_res['message'] != "Success":
            return False

        ids = [row[0] for row in ids_res['data']]

        # 遍历每个 ID_VAL，生成更新 SQL
        for id_val in ids:
            updates = []
            for col in columns:
                if col == 'C_02_INT':
                    updates.append("{} = {}".format(col, self.get_random(-214748, 214748)))
                elif col == 'C_03_DATE':
                    updates.append("{} = TO_DATE('{}', 'YYYY-MM-DD')".format(col, self.get_random_date()))
                elif col == 'C_01_VARCHAR':
                    updates.append("{} = '{}'".format(col, self.get_random_string(5)))

            # 生成 update SQL 语句
            update_sql = "UPDATE {}.{} SET {} WHERE ID = {}".format(db, table, ", ".join(updates), id_val)
            print(update_sql)

            # 执行更新操作
            update_res = self.exec_dameng_sql(db, update_sql)
            print(update_res)
            if update_res['message'] != "Success":
                return False
        return True

    def define_dml(self, db, table, lines):
        """
        批量生成 mysql dml 语句
        :param table:
        :param lines:
        :return:
        """
        insert_sql = ""
        while lines > 0:
            tmp = self.general_col_value(db, table)
            insert_sql = insert_sql + tmp + "\r\n"
            lines = lines - 1
        print(insert_sql)
        return insert_sql

    def commit_sql(self, db, table, num):
        """
        生成ddl、插入dml
        :param db:
        :param table:
        :param num:
        :return:
        """
        ddl_sql = self.define_ddl(table)
        self.exec_dameng_sql(db, ddl_sql)
        insert_sql = self.define_dml(db, table, num)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_dameng_sql(db, line)

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param db:
        :param table:
        :param num:
        :return:
        """

        # 删除表/新增表
        if is_recreate:
            ddl_sql = self.define_ddl(db, table)
            self.exec_dameng_sql(db, "drop table if exists {}.{};".format(db,table))
            self.exec_dameng_sql(db, ddl_sql)
        insert_sql = self.define_dml(db, table, num)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_dameng_sql(db, line)

    def get_count(self, db, table):
        sql = "select count(1) as cot from {}.{};".format(db, table)
        res = self.exec_dameng_sql(db, sql)
        print(res)
        res_num = res["data"][0][0]
        print(res_num)
        return res_num

    def exec_customer_sql(self, db, sql):
        res = self.exec_dameng_sql(db, sql)
        return res

    def get_db_data(self, db, table):
        sql = "SELECT * FROM {}.{} ORDER BY ID DESC LIMIT 10".format(db, table)
        res = self.exec_dameng_sql(db, sql)
        return self.set_return_massage(res, only_data="yes")



if __name__ == '__main__':
    a = DamengManager()

    # # 创建
    # sql = a.define_ddl("QA_SOURCE", "CDC_DAMENG_DML_T2")
    # a.exec_dameng_sql("QA_SOURCE", sql)
    #
    # # 写入
    # sql_template, values_list = a.generate_dml_sql("QA_SOURCE", "CDC_DAMENG_DML_T2", 2)
    # print(sql_template, values_list)
    # a.exec_dameng_sql("QA_SOURCE", sql_template, execute_type="batch", values=values_list)
    #
    # # 更新数据
    a.execute_update_data("QA_SOURCE", "CDC_DAMENG_DML_T2")

    # 删除数据
    # a.execute_delete_data("QA_SOURCE", "CDC_DAMENG_DML_T2")


    # 删除表
    # a.execute_drop_table("QA_SOURCE", "CDC_DAMENG_DML_T2")


